package me.keiwu.user.dao;

import me.keiwu.user.domain.UserLbs;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * Created by kei on 31/01/2018.
 */
public interface UserLbsDao extends JpaRepository<UserLbs, Long>, JpaSpecificationExecutor<UserLbs> {

//    @Query(value = "select count(*) from user_lbs", nativeQuery = true)
//    int countAll();

//    @Query(value = "select count(*) from user_lbs where city=?1 and district<>?2", nativeQuery = true)
//    int countByCityNotInDistrict(Integer city, Integer district);
//
//    @Query(value = "select count(*) from user_lbs where province=?1 and city<>?2", nativeQuery = true)
//    int countByProvinceNotInCity(Integer province, Integer city);
//
//
//    @Query(value = "select * from user_lbs where district=?1 limit ?2,?3", nativeQuery = true)
//    List<UserLbs> findAllByDistrict(Integer district, Integer offset, Integer size);
//
//    @Query(value = "select * from user_lbs where city=?1 and district<>?2 limit ?3,?4", nativeQuery = true)
//    List<UserLbs> findAllByCityNotInDistrict(Integer city, Integer district, Integer offset, Integer size);
//
//    @Query(value = "select * from user_lbs where province=?1 and city<>?2 limit ?3,?4", nativeQuery = true)
//    List<UserLbs> findAllByProvinceNotInCity(Integer province, Integer city, Integer offset, Integer size);




    // TODO
    // order by rand() 存在性能问题，后续优化
    @Query(value = "select count(*) from user_lbs where district=?1 and user_id not in (?2)", nativeQuery = true)
    int countByDistrict(Integer district, List<Long> userIds);

    @Query(value = "select count(*) from user_lbs where city=?1 and user_id not in (?2)", nativeQuery = true)
    int countByCity(Integer city, List<Long> userIds);

    @Query(value = "select count(*) from user_lbs where province=?1 and user_id not in (?2)", nativeQuery = true)
    int countByProvince(Integer province, List<Long> userIds);

    @Query(value = "select * from user_lbs where district=?1 and user_id not in (?2) order by rand() limit ?3", nativeQuery = true)
    List<UserLbs> findAllByDistrictRand(Integer district, List<Long> userIds, Integer size);

    @Query(value = "select * from user_lbs where city=?1 and user_id not in (?2) order by rand() limit ?3", nativeQuery = true)
    List<UserLbs> findAllByCityRand(Integer city, List<Long> userIds, Integer size);

    @Query(value = "select * from user_lbs where province=?1 and user_id not in (?2) order by rand() limit ?3", nativeQuery = true)
    List<UserLbs> findAllByProvinceRand(Integer province, List<Long> userIds, Integer size);

}
